/*DONE* Table [dbo].[<CompanyName>$Periodic Discount$5ecfc871-5d82-43f1-9c54-59685e82318d]  1/1  Script Date: 29.7.2020 - JI ******/
/*Add prestaging table for one specific company*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CreateTable as VARCHAR(MAX)
DECLARE @Company as VARCHAR(100)
DECLARE @CompanyID as INT

SET @CompanyID = X --The X is the value that needs to be replaced with the [Company] ID from LSInsight$Companies
SET @Company = (SELECT LSInsight$Companies.[CompanyPrefix] from dbo.LSInsight$Companies WHERE [Company] = @CompanyID)

IF @Company IS NOT NULL
BEGIN

DECLARE @DropTable as VARCHAR(MAX)
SET @DropTable = 'DROP TABLE IF EXISTS [dbo].['+@Company+'$Periodic Discount$5ecfc871-5d82-43f1-9c54-59685e82318d]'
EXEC(@DropTable)

SET @CreateTable = 'CREATE TABLE [dbo].['+@Company+'$Periodic Discount$5ecfc871-5d82-43f1-9c54-59685e82318d](
	[timestamp] [timestamp] NOT NULL,
	[No_] [nvarchar](20) NOT NULL,
	[Description] [nvarchar](30) NOT NULL,
	[Status] [int] NOT NULL,
	[Type] [int] NOT NULL,
	[Price Group] [nvarchar](10) NOT NULL,
	[Offer Type] [int] NOT NULL,
	[No_ Series] [nvarchar](10) NOT NULL,
	[Priority] [int] NOT NULL,
	[Block Infocode Discount] [tinyint] NOT NULL,
	[Block Sales Commission] [tinyint] NOT NULL,
	[Currency Code] [nvarchar](10) NOT NULL,
	[Validation Period ID] [nvarchar](10) NOT NULL,
	[Use Trans_ Line Time] [tinyint] NOT NULL,
	[Price Group Validation] [int] NOT NULL,
	[Sales Type Filter] [nvarchar](250) NOT NULL,
	[Discount Type] [int] NOT NULL,
	[Same_Diff_ M&M Lines] [int] NOT NULL,
	[Deal Price Value] [decimal](38, 20) NOT NULL,
	[Discount _ Value] [decimal](38, 20) NOT NULL,
	[Discount Amount Value] [decimal](38, 20) NOT NULL,
	[No_ of Least Expensive Items] [int] NOT NULL,
	[Disc_ _ of Least Expensive] [decimal](38, 20) NOT NULL,
	[Skip Least Exp_ Customer Opt_] [tinyint] NOT NULL,
	[No_ of Times Applicable] [int] NOT NULL,
	[Customer Disc_ Group] [nvarchar](20) NOT NULL,
	[Amount to Trigger] [decimal](38, 20) NOT NULL,
	[Member Value] [nvarchar](10) NOT NULL,
	[Pop-up Line 1] [nvarchar](80) NOT NULL,
	[Pop-up Line 2] [nvarchar](80) NOT NULL,
	[Pop-up Line 3] [nvarchar](80) NOT NULL,
	[Discount Tracking No_] [nvarchar](10) NOT NULL,
	[Last Date Modified] [datetime] NOT NULL,
	[Coupon Code] [nvarchar](10) NOT NULL,
	[Coupon Qty Needed] [decimal](38, 20) NOT NULL,
	[Member Type] [int] NOT NULL,
	[Member Attribute] [nvarchar](10) NOT NULL,
	[Member Attribute Value] [nvarchar](30) NOT NULL,
	[Block Manual Price Change] [tinyint] NOT NULL,
	[Block Line Discount Offer] [tinyint] NOT NULL,
	[Block Total Discount Offer] [tinyint] NOT NULL,
	[Block Tender Type Discount] [tinyint] NOT NULL,
	[Block Member Points] [tinyint] NOT NULL,
	[Block Printing] [tinyint] NOT NULL,
	[Buyer ID] [nvarchar](50) NOT NULL,
	[Buyer Group Code] [nvarchar](10) NOT NULL,
	[Maximum Discount Amount] [decimal](38, 20) NOT NULL,
	[Tender Type Code] [nvarchar](10) NOT NULL,
	[Tender Type Value] [nvarchar](10) NOT NULL,
	[Prompt for Action] [int] NOT NULL,
	[Tender Offer _] [decimal](38, 20) NOT NULL,
	[Tender Offer Amount] [decimal](38, 20) NOT NULL,
	[Line Discount Group Code] [nvarchar](10) NOT NULL,
	[Line Discount Execution] [int] NOT NULL,
	[Sequence Code] [int] NOT NULL,
	[Sequence Function] [int] NOT NULL,
	[Member Points] [decimal](38, 20) NOT NULL,
	[Planned Demand Type] [int] NOT NULL,
	[Planned Demand] [decimal](38, 20) NOT NULL,
	[Consider Calendar] [int] NOT NULL,
	[$systemId] [uniqueidentifier] NOT NULL,
 CONSTRAINT ['+@Company+'$Periodic Discount$5ecfc871-5d82-43f1-9c54-59685e82318d$Key1] PRIMARY KEY CLUSTERED 
(
	[No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT ['+@Company+'$Periodic Discount$5ecfc871-5d82-43f1-9c54-59685e82318d$$systemId] UNIQUE NONCLUSTERED 
(
	[$systemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'

EXEC(@CreateTable)

END